'''
Created on May 18, 2009

@author: mkiyer
'''

import os
import operator
import glob

from sqlalchemy import create_engine, MetaData, Table, Column, \
    String, Numeric, Date
from sqlalchemy.sql import select, and_, or_, not_

def reflect_sample_report_main_view(meta):
    '''
    "Column Name","Data Type","Nullable","Data Default","COLUMN ID","COMMENTS","INSERTABLE","UPDATABLE","DELETABLE"
    "SOLEXA_SAMPLE_ID","VARCHAR2(50)","No","","1","","NO","NO","NO"
    "SAMPLE_SOURCE_TYPE","VARCHAR2(50)","Yes","","2","","NO","NO","NO"
    "SAMPLE_SOURCE_TYPE_LOOKUP","NUMBER(10,0)","Yes","","3","","NO","NO","NO"
    "SAMPLE_SOURCE_ID","VARCHAR2(50)","No","","4","","NO","NO","NO"
    "SAMPLE_TYPE","VARCHAR2(50)","Yes","","5","","NO","NO","NO"
    "SAMPLE_TYPE_LOOKUP","NUMBER(10,0)","Yes","","6","","NO","NO","NO"
    "SAMPLE_DESC","VARCHAR2(255)","Yes","","7","","NO","NO","NO"
    "APP_TYPE","VARCHAR2(50)","Yes","","8","","NO","NO","NO"
    "APP_TYPE_LOOKUP","NUMBER(10,0)","Yes","","9","","NO","NO","NO"
    "SUB_DATE","VARCHAR2(19)","Yes","","10","","NO","NO","NO"
    "OWNER","VARCHAR2(50)","No","","11","","NO","NO","NO"
    "ND_CONC","NUMBER(10,2)","Yes","","12","","NO","NO","NO"
    "COMMENTS","VARCHAR2(3000)","Yes","","13","","NO","NO","NO"
    "SAMPLE_NAME","VARCHAR2(100)","No","","14","","NO","NO","NO"
    "EXP_DESIGN","VARCHAR2(50)","Yes","","15","","NO","NO","NO"
    "EXP_DESIGN_LOOKUP","NUMBER(10,0)","Yes","","16","","NO","NO","NO"
    "TISSUE_TYPE","VARCHAR2(50)","Yes","","17","","NO","NO","NO"
    "TISSUE_TYPE_LOOKUP","NUMBER(10,0)","Yes","","18","","NO","NO","NO"
    "TECH_TYPE","VARCHAR2(50)","Yes","","19","","NO","NO","NO"
    "TECH_TYPE_LOOKUP","NUMBER(10,0)","Yes","","20","","NO","NO","NO"
    "SAMPLE_STATUS","VARCHAR2(50)","Yes","","21","","NO","NO","NO"
    "SAMPLE_STATUS_LOOKUP","NUMBER(10,0)","Yes","","22","","NO","NO","NO"
    "PRE_ANALYSIS_ID","VARCHAR2(50)","Yes","","23","","NO","NO","NO"
    "RUN_DATE","DATE","Yes","","24","","NO","NO","NO"
    "GA","VARCHAR2(50)","Yes","","25","","NO","NO","NO"
    "FLOWCELL","VARCHAR2(50)","Yes","","26","","NO","NO","NO"
    "LANE_ID","NUMBER(10,0)","Yes","","27","","NO","NO","NO"
    "ANALYSIS_DATE","DATE","Yes","","28","","NO","NO","NO"
    "SW_VERSION","VARCHAR2(500)","Yes","","29","","NO","NO","NO"
    "DATA_PATH","VARCHAR2(2000)","Yes","","30","","NO","NO","NO"
    "ANALYSIS","VARCHAR2(100)","Yes","","31","","NO","NO","NO"
    "USE_BASE","VARCHAR2(2000)","Yes","","32","","NO","NO","NO"
    "READ_LENGTH","NUMBER(10,0)","Yes","","33","","NO","NO","NO"
    "SEED_LENGTH","NUMBER(10,0)","Yes","","34","","NO","NO","NO"
    "MAX_MATCH","NUMBER(10,0)","Yes","","35","","NO","NO","NO"
    "ELAND_GENOME","VARCHAR2(2000)","Yes","","36","","NO","NO","NO"
    "GENOME_CONTAM","VARCHAR2(2000)","Yes","","37","","NO","NO","NO"
    "GENOME_SPLICE","VARCHAR2(2000)","Yes","","38","","NO","NO","NO"
    "CLUSTER_RAW","NUMBER(10,0)","Yes","","39","","NO","NO","NO"
    "CLUSTER_PF","NUMBER(10,0)","Yes","","40","","NO","NO","NO"
    "ALIGN_PF","NUMBER(10,0)","Yes","","41","","NO","NO","NO"
    "ERR_RATE_PF","NUMBER(10,0)","Yes","","42","","NO","NO","NO"
    "SAMPLE_ID","VARCHAR2(25)","Yes","","43","","NO","NO","NO"
    "PARAM_ID","VARCHAR2(25)","Yes","","44","","NO","NO","NO"
    "PARAM_ORI_NAME","VARCHAR2(100)","Yes","","45","","NO","NO","NO"
    "PARAM_ORI_VALUE","VARCHAR2(100)","Yes","","46","","NO","NO","NO"
    '''
    mytable = Table('sample_report_main', meta,
                    Column('solexa_sample_id', String(50), nullable=False),
                    Column('sample_source_type', String(50), nullable=True),
                    Column('sample_source_type_lookup', Numeric(10,0), nullable=True),
                    Column('sample_source_id', String(50), nullable=False),
                    Column('sample_type', String(50), nullable=True),
                    Column('sample_type_lookup', Numeric(10,0), nullable=True),
                    Column('sample_desc', String(255), nullable=True),
                    Column('app_type', String(50), nullable=True),
                    Column('app_type_lookup', Numeric(10,0), nullable=True),
                    Column('sub_date', String(19), nullable=True),
                    Column('owner', String(50), nullable=False),
                    Column('nd_conc', Numeric(10,2), nullable=True),
                    Column('comments', String(3000), nullable=True),
                    Column('sample_name', String(100), nullable=False),
                    Column('exp_design', String(50), nullable=True),
                    Column('exp_design_lookup', Numeric(10,0), nullable=True),
                    Column('tissue_type', String(50), nullable=True),
                    Column('tissue_type_lookup', Numeric(10,0), nullable=True),
                    Column('tech_type', String(50), nullable=True),
                    Column('tech_type_lookup', Numeric(10,0), nullable=True),
                    Column('sample_status', String(50), nullable=True),
                    Column('sample_status_lookup', Numeric(10,0), nullable=True),
                    Column('pre_analysis_id', String(50), nullable=True),
                    Column('run_date', Date, nullable=True),
                    Column('ga', String(50), nullable=True),
                    Column('flowcell', String(50), nullable=True),
                    Column('lane_id', Numeric(10,0), nullable=True),
                    Column('analysis_date', Date, nullable=True),
                    Column('sw_version', String(500)),
                    Column('data_path', String(2000)),
                    Column('analysis', String(100)),
                    Column('use_base', String(2000)),
                    Column('read_length', Numeric(10,0)),
                    Column('seed_length', Numeric(10,0)),
                    Column('max_match', Numeric(10,0)),
                    Column('eland_genome', String(2000)),
                    Column('genome_contam', String(2000)),
                    Column('genome_splice', String(2000)),
                    Column('cluster_raw', Numeric(10,0)),
                    Column('cluster_pf', Numeric(10,0)),
                    Column('align_pf', Numeric(10,0)),
                    Column('err_rate_pf', Numeric(10,0)),
                    Column('sample_id', String(25)),
                    Column('param_id', String(25)),
                    Column('param_ori_name', String(100)),
                    Column('param_ori_value', String(100)),
                    schema='solexa_dp')
    return mytable

class SampleDB(object):
    '''
    interface to SampleDB
    '''
    paired_end_analysis_types = 'eland_pair'

    eland_formats = frozenset(['eland_query', 
                               'eland_result', 
                               'export', 
                               'sorted',
                               'eland_multi', 
                               'eland_extended'])
    
    mapping_formats = {'unique_best': ['eland_result',
                                       'export'],
                       'multi': ['eland_multi']
                       }
    
    def __init__(self, echo=False):
        '''
        creates an interface to sampleDB
        '''
        user = 'solexa_dp_ro'
        password = 'readonly'
        host = 'pathbio-db1'
        port = 1521
        service_name = 'O9DB1'
        # oracle
        oracle_db = create_engine('oracle://' + user + ':' + password + '@' + \
                                  host + ':' + str(port) + '/' + service_name,
                                  echo=echo)
        meta = MetaData()
        meta.bind = oracle_db
        sample_report_main_view = reflect_sample_report_main_view(meta)
        #meta.reflect(bind=oracle_db, schema='solexa_dp')
        #print meta.tables.keys()
        self.engine = oracle_db
        self.meta = meta
        self.samples = sample_report_main_view
    
    @staticmethod
    def get_available_formats(analysis_types):
        type_to_fmt = {'default': [],
                       'eland': ['eland_query',
                                 'eland_result'], 
                        'eland_extended': ['export',
                                           'sorted',
                                           'eland_query',
                                           'eland_multi',
                                           'eland_extended'],
                        'eland_rna': ['export',
                                      'sorted',
                                      'eland_extended'],
                        'eland_pair': ['export',
                                       'sorted',
                                       'eland_query',
                                       'eland_multi',
                                       'eland_extended']
                        }
        fmts = set([])
        for t in analysis_types:
            fmts.update(type_to_fmt[t])
        return fmts
    
    def get_flowcell_data_path(self, flowcell):
        '''
        connect to sampleDB and query flowcell ID to get sample path
        '''
        conn = self.engine.connect()
        query = self.samples.select()
        query = query.where(self.samples.c.flowcell==flowcell)
        result = conn.execute(query).fetchone()
        conn.close()
        return os.path.join(os.path.sep, result.data_path)

    def get_sample_analysis_types(self, flowcell, lane):
        conn = self.engine.connect()
        query = select([self.samples.c.analysis])        
        query = query.where(self.samples.c.flowcell==flowcell)
        query = query.where(self.samples.c.lane_id==lane)

        result = conn.execute(query).fetchall()
        analysis_types = frozenset(map(operator.attrgetter('analysis'), 
                                       result))
        conn.close()
        print result
        print analysis_types
        return analysis_types
    
    def get_sample_data_files(self, flowcell, lane, format, pair=None):
        conn = self.engine.connect()
        query = self.samples.select()
        query = query.where(self.samples.c.flowcell==flowcell)
        query = query.where(self.samples.c.lane_id==lane)
        result = conn.execute(query).fetchall()
        conn.close()

        if not pair:
            fnmatch_pattern = 's_%s*_%s.txt' % (str(lane), format)
        else:
            fnmatch_pattern = 's_%s_%s_%s.txt' % (str(lane), str(pair), format)
        
        filenames = []
        for row in result:
            basepath = os.path.join(os.path.sep, row.data_path)
            filenames.extend(glob.glob(os.path.join(basepath, fnmatch_pattern)))
        return filenames
        # get all the analysis types performed on this flowcell
#        analysis_types = frozenset(map(operator.attrgetter('analysis'), 
#                                       result))
#        available_formats = self.get_available_formats(analysis_types)
#        assert format in available_formats
#        if not pair:
#            filename = 's_%s_%s.txt' % (str(lane), format)
#        else:
#            filename = 's_%s_%s_%s.txt' % (str(lane), str(pair), format)
#
#        assert os.path.exists(filename)
#        if pair:
#            query = query.where(self.samples.analysis==paired_end_analysis_types)
#        for row in result:
#            print row
        #return os.path.join(os.path.sep, result.data_path)
        #basepath = get_flowcell_data_path(flowcell)
        #return os.path.join(basepath, _get_sample_filename(lane, pair, format))        

# temporarily store paths in a dict until DB connection is ready,
# this allows use of same API
_flowcell_paths = {
    '30351AAXX': '/archive07/090506_PATHBIO-SOLEXA1_30351AAXX/Data/IPAR_1.3/Bustard1.3.2_12-05-2009_root/GERALD_14-05-2009_root',
    '20E46AAXX': '/archive01/080519_PATHBIO-SOLEXA_20E46AAXX/Data/C1-36_Firecrest1.9.2_22-05-2008_root/Bustard1.9.2_22-05-2008_root/GERALD_22-05-2008_root',
    '20E6WAAXX': '/archive01/080331_PATHBIO-SOLEXA_20E6WAAXX/Data/C1-36_Firecrest1.8.28_04-04-2008_root/Bustard1.8.28_04-04-2008_root/GERALD_04-04-2008_root',
    '315A9AAXX': '/archive07/090416_PATHBIO-SOLEXA1_315A9AAXX/Data/IPAR_1.3/Bustard1.3.2_19-04-2009_root/GERALD_19-04-2009_root/',
    '206KMAAXX': '/archive04/081125_PATHBIO-SOLEXA_206KMAAXX/Data/C1-36_Firecrest1.9.6_29-11-2008_root/Bustard1.9.6_29-11-2008_root/GERALD_29-11-2008_root',
    '20A0VAAXX': '/archive04/081205_PATHBIO-SOLEXA_20A0VAAXX/Data/C1-36_Firecrest1.9.6_10-12-2008_root/Bustard1.9.6_10-12-2008_root/GERALD_10-12-2008_root',
    '20A9RAAXX': '/archive04/081021_HWI-EAS159_20A9RAAXX/Data/C1-36_Firecrest1.9.6_08-11-2008_root/Bustard1.9.6_08-11-2008_root/GERALD_08-11-2008_root',
    '301YWAAXX': '/archive07/090519_PATHBIO-SOLEXA1_301YWAAXX/Data/C1-84_Firecrest1.4.0_02-06-2009_root/Bustard1.4.0_02-06-2009_root/GERALD_02-06-2009_root',
    '20AV3AAXX': '/archive01/080229_PATHBIO-SOLEXA_20AV3AAXX/Data/C1-36_Firecrest1.8.28_04-03-2008_root/Bustard1.8.28_04-03-2008_root/GERALD_05-03-2008_root',
    '20F66AAXX': '/archive02/080523_PATHBIO-SOLEXA_20F66AAXX/Data/C1-36_Firecrest1.9.2_26-05-2008_root/Bustard1.9.2_26-05-2008_root/GERALD_26-05-2008_root',
    '20F0CAAXX': '/archive01/080505_PATHBIO-SOLEXA_20F0CAAXX/Data/C1-36_Firecrest1.9.2_09-05-2008_root/Bustard1.9.2_09-05-2008_root/GERALD_09-05-2008_root',
    '20LVAAAXX': '/archive01/080616_PATHBIO-SOLEXA_20LVAAAXX/Data/C1-36_Firecrest1.9.2_21-06-2008_root/Bustard1.9.2_21-06-2008_root/GERALD_21-06-2008_root',
    '20BPVAAXX': '/archive02/080623_PATHBIO-SOLEXA_20BPVAAXX/Data/C1-36_Firecrest1.9.2_27-06-2008_root/Bustard1.9.2_27-06-2008_root/GERALD_27-06-2008_root',
    '20FALAAXX': '/archive02/080725_PATHBIO-SOLEXA_20FALAAXX/Data/C1-36_Firecrest1.9.2_29-07-2008_root/Bustard1.9.2_29-07-2008_root/GERALD_29-07-2008_root',
    '20FFJAAXX': '/archive02/080828_PATHBIO-SOLEXA_20FFJAAXX/Data/C1-36_Firecrest1.9.2_03-09-2008_root/Bustard1.9.2_03-09-2008_root/GERALD_03-09-2008_root',
    '207B8AAXX': '/archive01/080611_HWI-EAS159_207B8AAXX/Data/C1-36_Firecrest1.9.2_20-06-2008_root/Bustard1.9.2_20-06-2008_root/GERALD_20-06-2008_root'
}


def get_flowcell_data_path(flowcell):
    '''
    connect to sampleDB and query flowcell ID to get sample path
    '''
    # TODO: connect to sampleDB Oracle backend and query database directly
    return _flowcell_paths.get(flowcell, None)

def _get_sample_filename(lane, pair, fileformat):
    assert (lane >= 1) and (lane <= 8)
    if pair:
        assert (pair == 1) or (pair == 2)
        pair = '_' + str(pair)
    else:
        pair = ''
    knownformats = set(['eland_result', 'export', 'sorted', 'eland_multi', 'eland_extended'])
    assert fileformat in knownformats
    return 's_' + str(lane) + pair + '_' + fileformat + '.txt'

def get_sample_data_path(flowcell, lane, pair, format):
    basepath = get_flowcell_data_path(flowcell)
    return os.path.join(basepath, _get_sample_filename(lane, pair, format))

def _convert_to_bed(infhd, format, bed_fhd, do_hpeak=False):
    if format == 'export':    
        convert_elandexport_2_bed(infhd, bed_fhd, do_hpeak)
    elif format == 'eland_result':
        convert_elandresult_2_bed(infhd, bed_fhd, do_hpeak)
    else:
        raise NameError('format ' + format + ' not supported yet')

class Sample(object):
    
    def __init__(self, name, fc, lane, is_paired, oldPipeline=False):
        self._name = name
        self._flowcell = fc
        self._lane = lane
        self._isPaired = is_paired
        self._oldPipeline = oldPipeline
    
    def __update(self):
        # TODO: lookup sample in sampleDB and update sample fields
        pass

    def getPath(self, format, pair=None):
        basepath = get_flowcell_data_path(self._flowcell)
        abspath = os.path.join(basepath, _get_sample_filename(self._lane, pair, format))
        assert os.path.isfile(abspath)
        return abspath

    def convertToBED(self, format, bed_fhd, do_hpeak=False):
        if sample.isPaired:
            s_path = sample.getPath(format, pair=1)
            _convert_to_bed(open(s_path), format, bed_fhd)
            s_path = sample.getPath(format, pair=2)
            _convert_to_bed(open(s_path), format, bed_fhd)
        else:
            s_path = sample.getPath(format)
            _convert_to_bed(open(s_path), format, bed_fhd)

    @property
    def name(self):
        return self._name
    
    # flowcell property
    def getflowcell(self):
        return self._flowcell
    def setflowcell(self, value):
        self._flowcell = value
    flowcell = property(getflowcell, setflowcell, None, "flowcell property")

    # lane property
    def getlane(self):
        return self._lane
    def setlane(self, lane):
        assert (lane >= 1) and (lane <= 8)
        self._lane = lane
    lane = property(getlane, setlane, None, "lane property")
    
    @property
    def isPaired(self):
        return self._isPaired

